<?php
/*******************************************************************************
 * 这个超过5000行，就基本失败了
 *******************************************************************************/
class Excel{
	protected $oPHPExcel = null;
	protected $chars = null;
	protected $activeSheet = null;
	
	public function __construct($prop = array()){
		include_once LIB_PTH . 'PHPExcel/PHPExcel.php';	// 放里面，避免重名冲突
		$this->oPHPExcel = new PHPExcel();	// 实例
		$this->chars = range('A', 'Z');
		
		$this->setProperties($prop);	// 设置属性，没生效
	}
	
	public function export($headers = array(), $datas = array(), $filename = 'export.xlsx', $sheet = 0, $sheetTitle = '导出数据'){
		if(empty($filename)){
			$filename = 'export.xlsx';
		}
		if(strpos($filename, '.xlsx') === false){
			$filename .= '.xlsx';
		}
		if(empty($headers)){
			$fields = array_keys($datas[0]);
			$headers = array_combine($fields, $fields);
		}
		
		$this->activeSheet = $this->oPHPExcel->setActiveSheetIndex($sheet);	// 活动表
		$this->freezePane();	// 冻结首行
		
		// 设置表头
		$j = 0;
		foreach($headers as $field => $sets){
			$this->setTextFormat($this->chars[$j]);	// 使用文本格式
			
			$cell = "{$this->chars[$j]}1";
			$this->activeSheet->setCellValue($cell, $sets['title']);
			$this->setBold($cell);
			if(isset($sets['width'])){
				$this->setWidth($this->chars[$j], $sets['width']);
			}
			if(isset($sets['wrap'])){
				$this->setWrap($this->chars[$j]);
			}
			$j ++;
		}
		
		// 添加数据
		$i = 2;
		foreach($datas as $row){
			$j = 0;
			foreach($headers as $field => $sets){
				$cell = "{$this->chars[$j]}{$i}";
				$this->oPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, ''.$row[$field]);
				$j ++;
			}
			$i ++;
		}
		
		// 设置第一个表为活动工作表
		//$this->oPHPExcel->setActiveSheetIndex(0);
		
		// 重命名
		$this->oPHPExcel
			->getActiveSheet()
			->setTitle($sheetTitle);
		
		// Redirect output to a client’s web browser (Excel2007)
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header("Content-Disposition: attachment;filename=\"{$filename}\"");
		header('Cache-Control: max-age=0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($this->oPHPExcel, 'Excel2007');
		$objWriter->save('php://output');
	}
	
	/***************************************************************************
	 * 其他方法
	 ***************************************************************************/
		
	public function setProperties($prop = array()){
		// 属性设置
		$this->oPHPExcel
			->getProperties()
			->setCreator("Leo")
			->setLastModifiedBy("Leo")
			->setTitle("XLSX")
			->setSubject("XLSX")
			->setDescription("export by PHP")
			->setKeywords("office 2007 openxml php")->setCategory("export");
	}
		
	public function freezePane($type = 'line'){
		if($type == 'line'){
			$this->activeSheet->FreezePane('A2');	// 冻结首行
		}elseif($type == 'col'){
			$this->activeSheet->FreezePane('B1');	// 冻结首列,A列
		}else{
			$this->activeSheet->FreezePane('B2');	// 冻结首行和首列
		}
	}
	
	public function setWidth($col, $width = 'auto'){
		if($width == 'auto' || $width == 0){
			$this->activeSheet->getColumnDimension($col)->setAutoSize(true);
		}else{
			$this->activeSheet->getColumnDimension($col)->setWidth($width);
		}
		return $this;
	}
	
	public function setWrap($col){
		$this->activeSheet->getStyle($col)->getAlignment()->setWrapText(true);
	}
	
	public function setBold($cell){
		$this->activeSheet->getStyle($cell)->getFont()->setBold(true);
		return $this;
	}
	
	public function setTextFormat($col){	// 使用文本格式
		$this->activeSheet->getStyle($col)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
		return $this; 
	}
}
?>